from tools.dbo import get_server_data_car, put_mysql_data, get_server_data, carried_sql, get_server_data_one


# 自动化样本更改自动化样本状态
def syn_auto_sample(mysql_conn,mysql_conn2):
    auto_sql = "select tube_code from sw_fz_freeze_tube where effective = 1"
    return_data = get_server_data_car(mysql_conn2, auto_sql)
    update_sample_sql = "update sample_info set is_auto_in_store = 1,status = 1 where code = %s"
    cursor = mysql_conn.cursor()
    cursor.executemany(update_sample_sql, return_data)
    mysql_conn.commit()
    lines = cursor.rowcount
    print(lines)




# 自动化样本盒子关联信息
def syn_auto_box_sample(sql_server_conn, mysql_conn):
    sql = "select tube_code,box_position_x,box_position_y,b.box_code,a.effective from sw_fz_freeze_tube a left join sw_fz_freeze_box b on a.box_id = b.id where a.create_time > '2021-03-25 14:33:54'"
    return_data = get_server_data_car(sql_server_conn, sql)
    print(return_data)
    list1 = []
    index = 0
    list2 = []
    for item in return_data:
        if item[4] == 2:
            continue
        sample_code = item[0]
        sql = "select id from sample_info where code = '"+sample_code+"'"
        sample_id = get_server_data_one(mysql_conn, sql)
        if sample_id != None:
            list2.append(sample_id[0])
        else:
            continue
        box_code = item[3]
        sql = "select id from eqm_freeze_box where box_code = '"+box_code+"'"
        box_id = get_server_data_one(mysql_conn, sql)
        if box_id == None:
            continue
        arr = (1,sample_id[0],box_id[0],item[1],item[2])
        list1.append(arr)
        index = index+1
        print(index)
    print(put_mysql_data(mysql_conn, "store_in_id,sample_id,box_id,position_row,position_column", "eqm_box_sample", list1))
    # update_sample_sql = "update sample_info set status=1 where id=%s"
    # cursor = mysql_conn.cursor()
    # cursor.executemany(update_sample_sql, list2)

# 自动化数据同步
def syn_auto(sql_server_conn, mysql_conn):
    sql_auto_box = "SELECT c.order_num,IF(d.order_num IS NULL,0,1) as aa,CONCAT(b.order_num, ',', d.order_num),a.box_code,a.box_type_id,a.box_name FROM sw_fz_freeze_box a LEFT JOIN sw_fz_frame_locator b ON a.frame_locator_id = b.id LEFT JOIN sw_fz_goods_shelves c on b.goods_shelves_id = c.id left JOIN sw_fz_frame d ON b.frame_id = d.id"
    return_data = get_server_data_car(sql_server_conn, sql_auto_box)
    list1 = []
    for item in return_data:
        if item[1] == 0:
            arr = (item[0],item[1],item[2],item[3],item[4]+12,item[5])
        else:
            arr = (item[0]+630,item[1],item[2],item[3],item[4]+12,item[5])
        list1.append(arr)
    print(put_mysql_data(mysql_conn, "shelf_id,status,position,box_code,box_type_id,box_name", "eqm_freeze_box", list1))
    print(return_data)
